﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class RecruitApplicantDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public RecruitApplicantDAL()
        {

        }
        public DataSet LoadApplicants(Nullable<int> Sequence, Nullable<int> EmployeeType
            , Nullable<int> EmployeeStatus, Nullable<int> EntType, string SearchText)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@ActiveStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@EmployeeStatus", EmployeeStatus);
                dbManager.AddParameters(3, "@EmployeeType", EmployeeType);
                dbManager.AddParameters(4, "@EntType", EntType);
                dbManager.AddParameters(5, "@SearchText", SearchText);

                strSQL = " SELECT e.* "
                     + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS TMP_EMP_TEXT"
                    + " FROM HRM_TMP_EMPLOYEES e"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " WHERE e.RECORD_STATUS = @ActiveStatus"
                    //+ " AND CASE WHEN @SearchText = '' THEN 0 = 0"
                    //    + " ELSE (e.FIRST_NAME LIKE CONCAT('%', @SearchText , '%')"
                    //        + " OR e.LAST_NAME LIKE CONCAT('%', @SearchText , '%')"
                    //        //+ " OR s.CLASS_SPEC_NAME LIKE CONCAT('%', @SearchText , '%')"
                    //        + ") END"
                    + ( String.IsNullOrEmpty(SearchText) ? "" : 
                        " AND ("
                            + " e.FIRST_NAME LIKE CONCAT('%', @SearchText , '%')"
                            + " OR e.LAST_NAME LIKE CONCAT('%', @SearchText , '%')"
                            + " OR s.CLASS_SPEC_NAME LIKE CONCAT('%', @SearchText , '%')"
                        + ")" )
                        + (Sequence.HasValue ? " AND e.TMP_EMP_SEQ = @Sequence" : "")
                        + (EmployeeStatus.HasValue ? " AND e.EMP_STATUS_SEQ = @EmployeeStatus" : "")
                        + (EmployeeType.HasValue ? " AND e.EMP_TYPE_SEQ = @EmployeeType" : "")
                        + (EntType.HasValue ? " AND e.ENT_TYPE_SEQ = @EntType" : "");

                return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataSet LoadEducations(Nullable<int> Sequence, Nullable<int> TmpEmployee)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ActiveStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@TmpEmployee", TmpEmployee);

                strSQL = " SELECT edu.*, qual.EDU_DEGREE_SEQ, de.EDU_DEGREE_NAME, major.EDU_MAJOR_NAME"
                        + ", ins.EDU_INS_NAME, qual.EDU_QUAL_NAME"
                    + " FROM HRM_TMP_EMPLOYEE_EDUCATIONS edu"
                        + " LEFT JOIN MAS_EDU_MAJORS major ON major.EDU_MAJOR_SEQ = edu.EDU_MAJOR_SEQ"
                        + " LEFT JOIN MAS_EDU_INSTITUTES ins ON ins.EDU_INS_SEQ = edu.EDU_INS_SEQ"
                        + " LEFT JOIN MAS_EDU_QUALIFICATIONS qual ON qual.EDU_QUAL_SEQ = edu.EDU_QUAL_SEQ"
                        + " LEFT JOIN MAS_EDU_DEGREES de ON qual.EDU_DEGREE_SEQ = de.EDU_DEGREE_SEQ"
                    + " WHERE edu.RECORD_STATUS = @ActiveStatus"
                    + (Sequence.HasValue ? " AND edu.TMP_EDU_SEQ = @Sequence" : "")
                    + (TmpEmployee.HasValue ? " AND edu.TMP_EMP_SEQ = @TmpEmployee" : "");

                return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void InsertEducations(int tmp_emp_seq, int edu_qual_seq, int edu_major_seq, int edu_ins_seq, int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@tmp_emp_seq", tmp_emp_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@created_by", created_by);
                dbManager.AddParameters(3, "@created_when", DateTime.Today);
                dbManager.AddParameters(4, "@edu_qual_seq", edu_qual_seq);
                dbManager.AddParameters(5, "@edu_major_seq", edu_major_seq);
                dbManager.AddParameters(6, "@edu_ins_seq", edu_ins_seq);

                string sql;

                sql = " INSERT INTO HRM_TMP_EMPLOYEE_EDUCATIONS"
                + "(TMP_EMP_SEQ,RECORD_STATUS,"
                + " CREATED_BY,CREATED_WHEN,EDU_QUAL_SEQ,EDU_MAJOR_SEQ,EDU_INS_SEQ) "
                + " values(@tmp_emp_seq,@record_status,@created_by,@created_when, "
                + " @edu_qual_seq,@edu_major_seq,@edu_ins_seq)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }
        }
        public void UpdateEducations(int Sequence, int edu_qual_seq, int edu_major_seq, int edu_ins_seq, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@updated_by", updated_by);
                dbManager.AddParameters(2, "@updated_when", DateTime.Today);
                dbManager.AddParameters(3, "@edu_qual_seq", edu_qual_seq);
                dbManager.AddParameters(4, "@edu_major_seq", edu_major_seq);
                dbManager.AddParameters(5, "@edu_ins_seq", edu_ins_seq);

                string sql;

                sql = " UPDATE HRM_TMP_EMPLOYEE_EDUCATIONS"
                    + " SET EDU_QUAL_SEQ = @edu_qual_seq"
                        + ", EDU_MAJOR_SEQ = @edu_major_seq"
                        + ", EDU_INS_SEQ = @edu_ins_seq"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE TMP_EDU_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }
        }
        public void DeleteEducations(int Sequence, int UpdatedBy)
        {
            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);                

                strSQL = " UPDATE HRM_TMP_EMPLOYEE_EDUCATIONS SET "
                  + " RECORD_STATUS=@DeleteRecord"
                  + ", UPDATED_BY = @UpdatedBy"
                  + ", UPDATED_WHEN = @UpdatedWhen"
                  + " WHERE TMP_EDU_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

    }
}
